/**** Deletion of Database Specific Foreign Key Constraints begins ****/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[FK_User_Question_Mapping_User_Info]') AND OBJECTPROPERTY(id, N'IsConstraint') = 1)
ALTER TABLE [dbo].[User_Question_Mapping]
DROP CONSTRAINT FK_User_Question_Mapping_User_Info
GO

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[FK_User_Question_Mapping_Question_Type]') AND OBJECTPROPERTY(id, N'IsConstraint') = 1)
ALTER TABLE [dbo].[User_Question_Mapping]
DROP CONSTRAINT FK_User_Question_Mapping_Question_Type
GO

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[FK_User_Question_Mapping_Answer_Type]') AND OBJECTPROPERTY(id, N'IsConstraint') = 1)
ALTER TABLE [dbo].[User_Question_Mapping]
DROP CONSTRAINT FK_User_Question_Mapping_Answer_Type
GO

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[FK_User_Role_Permission_User_Role_Type]') AND OBJECTPROPERTY(id, N'IsConstraint') = 1)
ALTER TABLE [dbo].[User_Role_Permission]
DROP CONSTRAINT FK_User_Role_Permission_User_Role_Type
GO

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[FK_User_Role_Permission_User_Permission_Type]') AND OBJECTPROPERTY(id, N'IsConstraint') = 1)
ALTER TABLE [dbo].[User_Role_Permission]
DROP CONSTRAINT FK_User_Role_Permission_User_Permission_Type
GO

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[FK_User_Role_User_Info]') AND OBJECTPROPERTY(id, N'IsConstraint') = 1)
ALTER TABLE [dbo].[User_Role]
DROP CONSTRAINT FK_User_Role_User_Info
GO

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[FK_User_Role_User_Role_Type]') AND OBJECTPROPERTY(id, N'IsConstraint') = 1)
ALTER TABLE [dbo].[User_Role]
DROP CONSTRAINT FK_User_Role_User_Role_Type
GO

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[FK_User_Type_User_Info]') AND OBJECTPROPERTY(id, N'IsConstraint') = 1)
ALTER TABLE [dbo].[User_Type]
DROP CONSTRAINT FK_User_Type_User_Info
GO

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[FK_User_Login_Failure_User_Info]') AND OBJECTPROPERTY(id, N'IsConstraint') = 1)
ALTER TABLE [dbo].[User_Login_Failure]
DROP CONSTRAINT FK_User_Login_Failure_User_Info
GO
/**** Deletion of Database Specific Foreign Key Constraints ends ****/
/**** Deletion of Database Tables begins ****/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[User_Info]') 
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[User_Info]
GO

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[User_Permission_Type]') 
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[User_Permission_Type]
GO

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[User_Role_Type]') 
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[User_Role_Type]
GO

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[User_Role_Permission]') 
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[User_Role_Permission]
GO

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[User_Role]') 
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[User_Role]
GO

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[User_Type]') 
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[User_Type]
GO
/**** Deletion of Database Tables ends ****/
/**** Creation of Database tables begins ****/
CREATE TABLE [dbo].[User_Info](
	[User_InfoID] [KeyOrIDField] IDENTITY(1,1) NOT NULL,
	[Username] [NameField] NOT NULL,
	[Password] [PasswordField] NOT NULL,
	[Retry_Password_Count] [TinyIntField] NOT NULL DEFAULT 0,
	[First_Name] [NameField] NOT NULL,
	[Middle_Name] [NameField] NULL,
	[Last_Name] [NameField] NOT NULL,
	[Is_Default] [BooleanField] NOT NULL DEFAULT 0,
	[Status] [MasterSmallIDField] NOT NULL ,
	[Status_Reason] [MediumTextField] NULL ,
	[Last_Logged_On] [DateTimeField] NULL DEFAULT GETDATE(),
	[Last_Logged_Out] [DateTimeField] NULL DEFAULT GETDATE(),
	[Last_Changed_Password] [DateTimeField] NULL DEFAULT GETDATE() ,
	[IP_Address] [SmallIdentifierField] NULL,
	[SessionID] [SmallConfigDataField] NULL,
	[Date_Of_Joining] [DateTimeField] NULL,
	[Date_Of_Relieving] [DateTimeField] NULL,
	[Date_Of_Birth] [DateTimeField] NULL,
	[Marital_Status] [SmallIdentifierField] NULL,
	[Gender] [SmallIdentifierField] NULL,
	[EmpCode] [SmallIdentifierField] NULL,
	[Exam_Score] [AmountField] NULL,
	[Exam_TypeID] [KeyOrIDField] NULL,
	[CreatedBy] [NameField] NOT NULL,
	[CreatedOn] [DateTimeField] NOT NULL DEFAULT GETDATE(),
	[ModifiedBy] [NameField] NULL,
	[ModifiedOn] [DateTimeField] NULL,
	[RFU1] [IdentifierField] NULL,
	[RFU2] [IdentifierField] NULL,
	[RFU3] [IdentifierField] NULL
) ON [PRIMARY]
GO

IF OBJECT_ID(N'[dbo].[User_Info]') IS NOT NULL
	PRINT '<<< CREATED TABLE [User_Info] >>>'
ELSE
	PRINT '<<< FAILED CREATING TABLE [User_Info] >>>'
GO

CREATE TABLE [dbo].[User_Permission_Type](
	[User_Permission_TypeID] [KeyOrIDField] IDENTITY(1,1) NOT NULL,
	[Name] [NameField] NOT NULL,
	[Description] [DescriptionField] NULL,
	[Status] [MasterSmallIDField] NOT NULL ,
	[Status_Reason] [MediumTextField] NULL ,
	[CreatedBy] [NameField] NOT NULL,
	[CreatedOn] [DateTimeField] NOT NULL DEFAULT GETDATE(),
	[ModifiedBy] [NameField] NULL,
	[ModifiedOn] [DateTimeField] NULL,
	[RFU1] [IdentifierField] NULL,
	[RFU2] [IdentifierField] NULL,
	[RFU3] [IdentifierField] NULL
) ON [PRIMARY]
GO

IF OBJECT_ID(N'[dbo].[User_Permission_Type]') IS NOT NULL
	PRINT '<<< CREATED TABLE [User_Permission_Type] >>>'
ELSE
	PRINT '<<< FAILED CREATING TABLE [User_Permission_Type] >>>'
GO

CREATE TABLE [dbo].[User_Role_Type](
	[User_Role_TypeID] [KeyOrIDField] IDENTITY(1,1) NOT NULL,
	[Name] [NameField] NOT NULL,
	[Description] [DescriptionField] NULL,
	[Status] [MasterSmallIDField] NOT NULL ,
	[Status_Reason] [MediumTextField] NULL ,
	[CreatedBy] [NameField] NOT NULL,
	[CreatedOn] [DateTimeField] NOT NULL DEFAULT GETDATE(),
	[ModifiedBy] [NameField] NULL,
	[ModifiedOn] [DateTimeField] NULL,
	[RFU1] [IdentifierField] NULL,
	[RFU2] [IdentifierField] NULL,
	[RFU3] [IdentifierField] NULL
) ON [PRIMARY]
GO

IF OBJECT_ID(N'[dbo].[User_Role_Type]') IS NOT NULL
	PRINT '<<< CREATED TABLE [User_Role_Type] >>>'
ELSE
	PRINT '<<< FAILED CREATING TABLE [User_Role_Type] >>>'
GO

CREATE TABLE [dbo].[User_Role_Permission](
	[User_Role_TypeID] [KeyOrIDField] NOT NULL,
	[User_Permission_TypeID] [KeyOrIDField] NOT NULL
) ON [PRIMARY]
GO

IF OBJECT_ID(N'[dbo].[User_Role_Permission]') IS NOT NULL
	PRINT '<<< CREATED TABLE [User_Role_Permission] >>>'
ELSE
	PRINT '<<< FAILED CREATING TABLE [User_Role_Permission] >>>'
GO

CREATE TABLE [dbo].[User_Role](
	[User_InfoID] [KeyOrIDField] NOT NULL,
	[User_Role_TypeID] [KeyOrIDField] NOT NULL
) ON [PRIMARY]
GO

IF OBJECT_ID(N'[dbo].[User_Role]') IS NOT NULL
	PRINT '<<< CREATED TABLE [User_Role] >>>'
ELSE
	PRINT '<<< FAILED CREATING TABLE [User_Role] >>>'
GO

CREATE TABLE [dbo].[User_Type](
	[User_InfoID] [KeyOrIDField] NOT NULL,
	[EntityID] [KeyOrIDField] NOT NULL
) ON [PRIMARY]
GO

IF OBJECT_ID(N'[dbo].[User_Type]') IS NOT NULL
	PRINT '<<< CREATED TABLE [User_Type] >>>'
ELSE
	PRINT '<<< FAILED CREATING TABLE [User_Type] >>>'
GO

CREATE TABLE [dbo].[User_Login_Failure](
	[User_InfoID] [KeyOrIDField] NOT NULL,
	[Attempted_On] [DateTimeField] NULL,
	[IP_Address] [SmallIdentifierField] NULL,
	[CreatedBy] [NameField] NOT NULL,
	[CreatedOn] [DateTimeField] NOT NULL DEFAULT GETDATE(),
	[ModifiedBy] [NameField] NULL,
	[ModifiedOn] [DateTimeField] NULL,
	[RFU1] [IdentifierField] NULL,
	[RFU2] [IdentifierField] NULL,
	[RFU3] [IdentifierField] NULL
) ON [PRIMARY]
GO

IF OBJECT_ID(N'[dbo].[User_Login_Failure]') IS NOT NULL
	PRINT '<<< CREATED TABLE [User_Login_Failure] >>>'
ELSE
	PRINT '<<< FAILED CREATING TABLE [User_Login_Failure] >>>'
GO

CREATE TABLE [dbo].[User_Question_Mapping](
	[User_InfoID] [KeyOrIDField] NOT NULL,
	[Question_TypeID] [KeyOrIDField] NOT NULL,
	[Marked_Answer_TypeID] [KeyOrIDField] NULL,
	[CreatedBy] [NameField] NOT NULL,
	[CreatedOn] [DateTimeField] NOT NULL DEFAULT GETDATE(),
	[ModifiedBy] [NameField] NULL,
	[ModifiedOn] [DateTimeField] NULL,
	[RFU1] [IdentifierField] NULL,
	[RFU2] [IdentifierField] NULL,
	[RFU3] [IdentifierField] NULL
) ON [PRIMARY]
GO

IF OBJECT_ID(N'[dbo].[User_Question_Mapping]') IS NOT NULL
	PRINT '<<< CREATED TABLE [User_Question_Mapping] >>>'
ELSE
	PRINT '<<< FAILED CREATING TABLE [User_Question_Mapping] >>>'
GO
/**** Creation of Database tables ends ****/
/**** Creation of Database Primary keys begins ****/
ALTER TABLE [dbo].[User_Info] WITH NOCHECK 
ADD CONSTRAINT [PK_User_Info] PRIMARY KEY CLUSTERED (
	[User_InfoID]
) ON [PRIMARY] 
GO

ALTER TABLE [dbo].[User_Question_Mapping] WITH NOCHECK 
ADD CONSTRAINT [PK_User_Question_Mapping] PRIMARY KEY CLUSTERED (
	[User_InfoID],
	[Question_TypeID]
) ON [PRIMARY] 
GO

ALTER TABLE [dbo].[User_Permission_Type] WITH NOCHECK 
ADD CONSTRAINT [PK_User_Permission_Type] PRIMARY KEY CLUSTERED (
	[User_Permission_TypeID]
) ON [PRIMARY] 
GO

ALTER TABLE [dbo].[User_Role_Type] WITH NOCHECK 
ADD CONSTRAINT [PK_User_Role_Type] PRIMARY KEY CLUSTERED (
	[User_Role_TypeID]
) ON [PRIMARY] 
GO

ALTER TABLE [dbo].[User_Role_Permission] WITH NOCHECK 
ADD CONSTRAINT [PK_User_Role_Permission] PRIMARY KEY CLUSTERED (
	[User_Role_TypeID],
	[User_Permission_TypeID]
) ON [PRIMARY] 
GO

ALTER TABLE [dbo].[User_Role] WITH NOCHECK 
ADD CONSTRAINT [PK_User_Role] PRIMARY KEY CLUSTERED (
	[User_InfoID],
	[User_Role_TypeID]
) ON [PRIMARY] 
GO

ALTER TABLE [dbo].[User_Type] WITH NOCHECK 
ADD CONSTRAINT [PK_User_Type] PRIMARY KEY CLUSTERED (
	[User_InfoID],
	[EntityID]
) ON [PRIMARY] 
GO
/**** Creation of Database Primary keys ends ****/
/**** Creation of Database Foreign keys begins ****/
ALTER TABLE [dbo].[User_Role_Permission] WITH NOCHECK 
ADD CONSTRAINT [FK_User_Role_Permission_User_Role_Type] FOREIGN KEY (
	[User_Role_TypeID]
) REFERENCES [dbo].[User_Role_Type] (
	[User_Role_TypeID]
)
GO

ALTER TABLE [dbo].[User_Role_Permission] WITH NOCHECK 
ADD CONSTRAINT [FK_User_Role_Permission_User_Permission_Type] FOREIGN KEY (
	[User_Permission_TypeID]
) REFERENCES [dbo].[User_Permission_Type] (
	[User_Permission_TypeID]
)
GO

ALTER TABLE [dbo].[User_Role] WITH NOCHECK 
ADD CONSTRAINT [FK_User_Role_User_Info] FOREIGN KEY (
	[User_InfoID]
) REFERENCES [dbo].[User_Info] (
	[User_InfoID]
)
GO

ALTER TABLE [dbo].[User_Role] WITH NOCHECK 
ADD CONSTRAINT [FK_User_Role_User_Role_Type] FOREIGN KEY (
	[User_Role_TypeID]
) REFERENCES [dbo].[User_Role_Type] (
	[User_Role_TypeID]
)
GO

ALTER TABLE [dbo].[User_Type] WITH NOCHECK 
ADD CONSTRAINT [FK_User_Type_User_Info] FOREIGN KEY (
	[User_InfoID]
) REFERENCES [dbo].[User_Info] (
	[User_InfoID]
)
GO

ALTER TABLE [dbo].[User_Login_Failure] WITH NOCHECK 
ADD CONSTRAINT [FK_User_Login_Failure_User_Info] FOREIGN KEY (
	[User_InfoID]
) REFERENCES [dbo].[User_Info] (
	[User_InfoID]
)
GO

ALTER TABLE [dbo].[User_Question_Mapping] WITH NOCHECK 
ADD CONSTRAINT [FK_User_Question_Mapping_User_Info] FOREIGN KEY (
	[User_InfoID]
) REFERENCES [dbo].[User_Info] (
	[User_InfoID]
)
GO

ALTER TABLE [dbo].[User_Question_Mapping] WITH NOCHECK 
ADD CONSTRAINT [FK_User_Question_Mapping_Question_Type] FOREIGN KEY (
	[Question_TypeID]
) REFERENCES [dbo].[Question_Type] (
	[Question_TypeID]
)
GO

ALTER TABLE [dbo].[User_Question_Mapping] WITH NOCHECK 
ADD CONSTRAINT [FK_User_Question_Mapping_Answer_Type] FOREIGN KEY (
	[Marked_Answer_TypeID]
) REFERENCES [dbo].[Answer_Type] (
	[Answer_TypeID]
)
GO
/**** Creation of Database Foreign keys ends ****/